﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ForumDAL
{
    public class CategoryDAL
    {
        public List<ForumDomainModels.Category> GetCategoriesBySection(ForumDomainModels.Section section)
        {
            string connectionString = Properties.Settings.Default.connectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT TOP 1000 [Id],[CategoryName],[SectionId],[Description] " +
                    " FROM [ForumDb].[dbo].[Categories] "+
                    "WHERE SectionId = @sectionId";
                using (SqlCommand command = new SqlCommand(sql,connection))
                {
                    command.Parameters.Add(new SqlParameter("sectionId", section.Id));
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                        {
                            throw new CustomExceptions.NoRowsRetrieved();
                        }
                        else
                        {
                            List<ForumDomainModels.Category> categories = new List<ForumDomainModels.Category>();
                            while (reader.Read())
                            {
                                ForumDomainModels.Mappers.CategoryMapper categoryMapper = 
                                    new ForumDomainModels.Mappers.CategoryMapper();
                                ForumDomainModels.Category currentCategory = categoryMapper.DataFromDatabaseToCategoryDomainModel(reader);
                                categories.Add(currentCategory);
                            }
                            return categories;
                        }
                    }
                }

            }
        }

        public ForumDomainModels.Category GetCategoryById(ForumDomainModels.Category category)
        {
            string connectionString = Properties.Settings.Default.connectionString;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                string sql = "SELECT [Id],[CategoryName],[SectionId],[Description] " +
                    "FROM [ForumDb].[dbo].[Categories] "+
                    "WHERE Id = @id";
                using (SqlCommand command = new SqlCommand(sql,connection))
                {
                    command.Parameters.Add("id", category.Id);
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (!reader.HasRows)
                        {
                            throw new CustomExceptions.NoRowsRetrieved();
                        }
                        else
                        {
                            ForumDomainModels.Category cat = new ForumDomainModels.Category();
                            reader.Read();
                            cat.Id = (int)reader["Id"];
                            cat.CategoryName = (string)reader["CategoryName"];
                            cat.Description = (string)reader["Description"];
                            cat.SectionId = (int)reader["SectionId"];
                            return cat;
                        }
                    }
                }
            }
        }
    }
}
